package jpa.service.synchro.staging;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.persistence.PersistenceContexts;
import javax.persistence.PersistenceUnit;
import javax.persistence.Query;

import jpa.entitys.synchro.common.CommonEntity;
import jpa.entitys.synchro.staging.SyncQueue;
import jpa.entitys.synchro.staging.SyncQueueParam;
import jpa.service.synchro.common.JPAService;

import org.apache.log4j.Logger;

import promis.db.connection.dbHelper;
import promis.utility.CommonService;
import promis.utility.ProcessLevel;
import promis.utility.Utilitys;

public class SyncQueueServices extends JPAService {
	
	private static final Logger log = Logger.getLogger(SyncQueueServices.class);
	
	public SyncQueueServices(EntityManager em) {
		super(em);
	}
	
	public void MoveQueueToBack(List<BigDecimal> lst) throws Exception{
		Connection conn = null;
		try{		
			conn = new dbHelper().getConn();
			if(CommonService.getSize(lst) > 0){				
				for(BigDecimal queue_id : lst){
					try{
						
						MoveQueueToBack(queue_id, conn);
						
					}catch(Exception e){
						e.printStackTrace();
						log.error(e.getMessage());
						System.out.println(e.getMessage());
					}
				}
			}
		}catch(Exception e){
			log.error(e.getMessage());
			System.out.println(e.getMessage());
			e.printStackTrace();
			throw e;
		}finally{	
			if(conn != null){
				if(!conn.isClosed())
					conn.close();	
			}
		}
	}
	
	public void MoveQueueToBack(BigDecimal queue_id) throws Exception{
		Connection conn = new dbHelper().getConn();
		
		try{
			
			MoveQueueToBack(queue_id, conn);
			
		}catch(Exception e){
			log.error(e.getMessage());
			System.out.println(e.getMessage());
			e.printStackTrace();
			throw e;
		}finally{			
			if(!conn.isClosed())
				conn.close();
		}
	}
	
	public void MoveQueueToBack(BigDecimal queue_id, Connection conn) throws Exception{
		try{
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			String sql = "";
			
			sql = " INSERT INTO SYNC_QUEUE VALUE (SELECT * FROM  SYNC_QUEUE_LOGS WHERE QUEUE_ID  = "+ queue_id +") ";
			stmt.executeUpdate(sql);
			
			sql = " DELETE FROM SYNC_QUEUE_LOGS WHERE QUEUE_ID = "+ queue_id +" ";
			stmt.executeUpdate(sql);

			sql = " INSERT INTO SYNC_QUEUE_PARAM VALUE (SELECT * FROM SYNC_QUEUE_PARAM_LOGS WHERE QUEUE_ID  = "+ queue_id +") ";
			stmt.executeUpdate(sql);
			
			sql = " DELETE FROM SYNC_QUEUE_PARAM_LOGS WHERE QUEUE_ID = " + queue_id;
			stmt.executeUpdate(sql);
			
			sql = " UPDATE SYNC_QUEUE SET IS_PROCESSING = 'N', PROCESS_CODE = NULL, PROCESS_DESC = NULL, " +
					" PROC_START_TIME = NULL, PROC_END_TIME = NULL, USE_MINUTE = NULL, " + 
					" USE_SECOND = NULL, PROCESS_LEVEL = NULL, PMS_USE_START = NULL, " +
					" PMS_USE_END = NULL, SEND_USE_START = NULL, SEND_USE_END = NULL, REF_INSTANCE_NO = NULL, PROC_USE_TIME = NULL " +
					" WHERE IS_PROCESSING = 'Y' AND QUEUE_ID = " + queue_id;
			stmt.executeUpdate(sql);
			
			sql = " UPDATE SYNC_QUEUE_PARAM SET IS_PROCESSING = 'N' WHERE IS_PROCESSING = 'Y' AND QUEUE_ID = " + queue_id;
			stmt.executeUpdate(sql);
			
			System.out.println("Move Back Queue Id =" +queue_id + " Success.");
			conn.commit();
            stmt.close();
			
		}catch(Exception e){
			log.error(e.getMessage());
			System.out.println(e.getMessage());
			e.printStackTrace();
			throw e;
		}finally{
			conn.setAutoCommit(true);
			/*
			if(!conn.isClosed())
				conn.close();
			*/
		}
	}
	
	public void UPD_PROMISUseTime(BigDecimal queue_id, Connection conn, boolean isStartTime, boolean isStopTime, ProcessLevel processLevel, String refInstanceNo) throws SQLException{
		try{
			
		conn.setAutoCommit(false);
		Statement stmt = conn.createStatement();
		StringBuilder _strQuery = new StringBuilder();
		
		_strQuery.append(" UPDATE SYNCHRO.SYNC_QUEUE SET PROC_UPDATEDON = CURRENT_DATE ");
		
		if(!CommonService.IsNull(refInstanceNo))
			_strQuery.append(" ,REF_INSTANCE_NO = " + Utilitys.strSql(refInstanceNo));
		if(processLevel != null)
			_strQuery.append(" ,PROCESS_LEVEL = " + Utilitys.strSql(processLevel.toString()));
		if(isStartTime)
			_strQuery.append(" ,PMS_USE_START = CURRENT_DATE ");
		if(isStopTime)
			_strQuery.append(" ,PMS_USE_END = CURRENT_DATE ");
		
		_strQuery.append(" WHERE QUEUE_ID = ").append(queue_id);
		
		System.out.println(_strQuery.toString());		
		
		stmt.executeUpdate(_strQuery.toString());
		conn.commit();
        stmt.close();
        
		}catch(Exception e){
			conn.rollback();
			System.out.println(e.getMessage());
			log.error(e.getMessage());
			e.printStackTrace();
        }finally{
			conn.setAutoCommit(true);
		}
	}
	
	public void UPD_SENDUseTime(BigDecimal queue_id, Connection conn, boolean isStartTime, boolean isStopTime, ProcessLevel processLevel, String refInstanceNo) throws SQLException{
		try{
			
		conn.setAutoCommit(false);
		Statement stmt = conn.createStatement();
		StringBuilder _strQuery = new StringBuilder();
		
		_strQuery.append(" UPDATE SYNCHRO.SYNC_QUEUE SET PROC_UPDATEDON = CURRENT_DATE ");
		
		if(!CommonService.IsNull(refInstanceNo))
			_strQuery.append(" ,REF_INSTANCE_NO = " + Utilitys.strSql(refInstanceNo));
		if(processLevel != null)
			_strQuery.append(" ,PROCESS_LEVEL = " + Utilitys.strSql(processLevel.toString()));		
		if(isStartTime)
			_strQuery.append(" ,SEND_USE_START = CURRENT_DATE ");
		if(isStopTime)
			_strQuery.append(" ,SEND_USE_END = CURRENT_DATE ");
		
		_strQuery.append(" WHERE QUEUE_ID = ").append(queue_id);
		
		System.out.println(_strQuery.toString());
		
		stmt.executeUpdate(_strQuery.toString());
		conn.commit();
        stmt.close();
        
		}catch(Exception e){
			conn.rollback();
			System.out.println(e.getMessage());
			log.error(e.getMessage());
			e.printStackTrace();
        }finally{
			conn.setAutoCommit(true);
		}
	}
	
	public void UPD_SYNCUseTime(BigDecimal queue_id, Connection conn, boolean isStartTime, boolean isStopTime, ProcessLevel processLevel, String refInstanceNo) throws SQLException{
		try{
			
		conn.setAutoCommit(false);
		Statement stmt = conn.createStatement();
		StringBuilder _strQuery = new StringBuilder();
		
		_strQuery.append(" UPDATE SYNCHRO.SYNC_QUEUE SET PROC_UPDATEDON = CURRENT_DATE ");
		
		if(!CommonService.IsNull(refInstanceNo))
			_strQuery.append(" ,REF_INSTANCE_NO = " + Utilitys.strSql(refInstanceNo));
		if(processLevel != null)
			_strQuery.append(" ,PROCESS_LEVEL = " + Utilitys.strSql(processLevel.toString()));
		if(isStartTime)
			_strQuery.append(" ,PROC_START_TIME = CURRENT_DATE ");
		if(isStopTime){
			_strQuery.append(" ,PROC_END_TIME = CURRENT_DATE ");
			_strQuery.append(" ,PROC_USE_TIME = F_CALDATE(PROC_START_TIME, CURRENT_DATE) ");
		}
		
		_strQuery.append(" WHERE QUEUE_ID = ").append(queue_id);
		
		System.out.println(_strQuery.toString());	
		stmt.executeUpdate(_strQuery.toString());		
		conn.commit();
        stmt.close();
        
		}catch(Exception e){
			conn.rollback();
			System.out.println(e.getMessage());
			log.error(e.getMessage());
			e.printStackTrace();
        }finally{
			conn.setAutoCommit(true);
		}
	}
	
	public void MoveQueueToLogs(BigDecimal queue_id, Connection conn)throws Exception {
		
		if(CommonService.IsNull(queue_id)) return;
		
		try{
			
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			String sql = "";
					
			sql = "";
			sql = (" INSERT INTO SYNCHRO.SYNC_QUEUE_LOGS VALUE (SELECT * FROM SYNCHRO.SYNC_QUEUE WHERE NVL(IS_PROCESSING,'N') = 'Y' AND QUEUE_ID = "+ queue_id +" ) ");
			stmt.executeUpdate(sql);
			
			sql = "";
			sql = (" DELETE SYNCHRO.SYNC_QUEUE WHERE NVL(IS_PROCESSING,'N') = 'Y' AND QUEUE_ID = "+ queue_id +" ");
			stmt.executeUpdate(sql);
			
			sql = "";
			sql = (" INSERT INTO SYNCHRO.SYNC_QUEUE_PARAM_LOGS VALUE (SELECT * FROM SYNCHRO.SYNC_QUEUE_PARAM WHERE  NVL(IS_PROCESSING,'N') = 'Y'  AND QUEUE_ID = "+ queue_id +" ) ");
			stmt.executeUpdate(sql);
			
			sql = "";
			sql = (" DELETE SYNCHRO.SYNC_QUEUE_PARAM WHERE NVL(IS_PROCESSING,'N') = 'Y'  AND QUEUE_ID = "+ queue_id +" ");
			stmt.executeUpdate(sql);				
			
            // Executing executeBatch
            //stmt.executeBatch();
            conn.commit();
            stmt.close();
			
		}catch(Exception e){
			conn.rollback();
			System.out.println(e.getMessage());
			log.error(e.getMessage());
			e.printStackTrace();
		}finally{
			conn.setAutoCommit(true);
			/*
			if(!conn.isClosed())
				conn.close();
			 */
		}
	}
	
	public void MoveQueueToLogs()throws Exception {
		Connection conn = new dbHelper().getConn();
		try{
						
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			String sql = "";
					
			sql = "";
			sql = (" INSERT INTO SYNCHRO.SYNC_QUEUE_LOGS VALUE (SELECT * FROM SYNCHRO.SYNC_QUEUE WHERE NVL(IS_PROCESSING,'N') = 'Y' ) ");
			stmt.executeUpdate(sql);
			
			sql = "";
			sql = (" DELETE SYNCHRO.SYNC_QUEUE WHERE NVL(IS_PROCESSING,'N') = 'Y' ");
			stmt.executeUpdate(sql);
			
			sql = "";
			sql = (" INSERT INTO SYNCHRO.SYNC_QUEUE_PARAM_LOGS VALUE (SELECT * FROM SYNCHRO.SYNC_QUEUE_PARAM WHERE  NVL(IS_PROCESSING,'N') = 'Y' ) ");
			stmt.executeUpdate(sql);
			
			sql = "";
			sql = (" DELETE SYNCHRO.SYNC_QUEUE_PARAM WHERE NVL(IS_PROCESSING,'N') = 'Y' ");
			stmt.executeUpdate(sql);
			
            // Executing executeBatch
            // stmt.executeBatch();
            conn.commit();
            stmt.close();
			
		}catch(Exception e){
			conn.rollback();
			System.out.println(e.getMessage());
			log.error(e.getMessage());
		}finally{
			conn.setAutoCommit(true);
			
			if(!conn.isClosed())
				conn.close();
		}
	}
	
	public SyncQueue findMin() throws Exception {	
		EntityTransaction t = null;
		SyncQueue queue = null;
		try{		
			t = super.getEm().getTransaction();	        	        
	        t.begin();
	        
			Query query = super.getEm().createNamedQuery("SyncQueue.findMin");
			List<CommonEntity> lst = query.getResultList();
			if(CommonService.getSize(lst) == 1){
				
				queue = (SyncQueue) lst.get(0);
				queue.setProcessLevel(ProcessLevel.INIT_LEVEL.toString());
				
				//Update
				super.getEm().merge(queue);
			}
			
			t.commit();
			//DR.TREE Add commander 2013-11-20
			//super.getEm().flush();
			
		}catch(Exception e){			
			log.error(e.getMessage());
			t.rollback();
			e.printStackTrace();
		}finally{
			if (t.isActive()) 
				  t.rollback();
			
			if(queue != null)
				System.out.println("Find & Commit QueueId =" + queue.getQueueId() + " " + queue.getProcessLevel());		
		}
		return queue;
	}
	
	public SyncQueue findMax() throws Exception {		
		EntityTransaction t = null;
		SyncQueue queue = null;
		try{		
			t = super.getEm().getTransaction();	
	        t.begin();
	        
			Query query = super.getEm().createNamedQuery("SyncQueue.findMax");
			List<CommonEntity> lst = query.getResultList();
			if(CommonService.getSize(lst) == 1){
				
				queue = (SyncQueue) lst.get(0);				
				queue.setProcessLevel(ProcessLevel.INIT_LEVEL.toString());
				
				//Update 
				super.getEm().merge(queue);
			}
			
			t.commit();
			
		}catch(Exception e){			
			log.error(e.getMessage());
			t.rollback();
			e.printStackTrace();
		}finally{
			if (t.isActive()) 
				  t.rollback();
			
			if(queue != null)
				System.out.println("Find & Commit QueueId =" + queue.getQueueId() + " " + queue.getProcessLevel());
		}
		return queue;	
	}
	
	public SyncQueue findById(String id) throws Exception {				
		return super.ConvertToYoureObject(super.find(id, "SyncQueue.findById"));			
	}
	
	public SyncQueue Inserted(SyncQueue queue) throws Exception {
		try{
			List<CommonEntity> common_lst = new ArrayList<CommonEntity>();			
			if(CommonService.IsNull(queue.getQueueId()))
			{
				String id = super.SequenceNext("SYNC_QUEUE");					
				queue.setQueueId(CommonService.ConvertStringToBigDecimal(id));
				queue.setQueueSequence(CommonService.ConvertStringToBigDecimal(id));
				queue.setProcCreatedby("PROC-QUEUE");
				queue.setProcCreatedon(this.getCurrent_date());
				//queue.setProcUpdatedby("PROC-QUEUE");
				//queue.setProcUpdatedon(this.getCurrent_date());
				
				queue.setIsActive("Y");
				common_lst.add(queue);
			}
			super.inserted(common_lst);
			
		}catch(Exception e)
		{
			log.error(e.getMessage());
			System.out.println(e.getMessage());
			throw e;
		}
		return queue;
	}
	
	public SyncQueue Inserted(SyncQueue queue, List<SyncQueueParam> vlauelist) throws Exception {
		
		try{
			Date dtime = this.getCurrent_date();
			if(queue == null || CommonService.getSize(vlauelist) <= 0) {
				throw new Exception("Nothing data");
			}else{
				if(CommonService.IsNull(queue.getQueueId()))
				{
					String id = super.SequenceNext("SYNC_QUEUE");					
					queue.setQueueId(CommonService.ConvertStringToBigDecimal(id));
					queue.setQueueSequence(CommonService.ConvertStringToBigDecimal(id));
					queue.setProcCreatedby("PROC-QUEUE");
					queue.setProcCreatedon(dtime);
					
					//queue.setProcUpdatedby("PROC-QUEUE");
					//queue.setProcUpdatedon(dtime);
					
					queue.setIsActive("Y");
				}else 
					throw new Exception("QueueId is not emty ::: " + queue.getQueueId());
				
				List<CommonEntity> common_lst = new ArrayList<CommonEntity>();			
				for(SyncQueueParam obj : vlauelist)
				{
					if(CommonService.ConvertStringToLong(obj.getSyncQueueParamId()) <= 0)
					{
						String id = super.SequenceNext("SYNC_QUEUE_PARAM");
						obj.setQueueId(queue.getQueueId());
						obj.setSyncQueueParamId(CommonService.ConvertStringToBigDecimal(id));
						obj.setProcCreatedby("PROC-QUEUE");
						obj.setProcCreatedon(dtime);
						common_lst.add(obj);
					}
				}
				
				//*************************************************************************
				EntityTransaction t = null;
				try{			
			        t = super.getEm().getTransaction();	        	        
			        t.begin();

			        //Queue
			        super.getEm().persist(queue); 
			        
			        //Queue line param
			        for(CommonEntity obj : common_lst){
			        	super.getEm().persist(obj); 
			        }
			        
			        t.commit();
				}finally{
					if (t.isActive()) 
						  t.rollback();
				}
				//*************************************************************************
			}
		}catch(Exception e)
		{
			log.error(e.getMessage());
			System.out.println(e.getMessage());
			throw e;
		}
		return queue;
	}
	
	public SyncQueue Updated(SyncQueue queue, List<SyncQueueParam> vlauelist) throws Exception {		
		try{
			
			Date dtime = this.getCurrent_date();
			if(queue == null || CommonService.getSize(vlauelist) <= 0) {
				throw new Exception("Nothing data");
			}else{

				if(!CommonService.IsNull(queue.getQueueId()))
				{
					queue.setProcUpdatedby("PROC-QUEUE");
					queue.setProcUpdatedon(dtime);
				}else 
					throw new Exception("QueueId is emty ::: " + queue.getQueueId());
				
				List<CommonEntity> common_lst = new ArrayList<CommonEntity>();			
				for(SyncQueueParam obj : vlauelist)
					common_lst.add(obj);
				
				//*************************************************************************
				EntityTransaction t = null;
				try{			
			        t = super.getEm().getTransaction();	        	        
			        t.begin();

			        //Queue
			        super.getEm().merge(queue); 
			        
			        //Queue line param
			        for(CommonEntity obj : common_lst){
			        	super.getEm().merge(obj); 
			        }
			        
			        t.commit();
				}finally{
					if (t.isActive()) 
						  t.rollback();
				}
				//*************************************************************************
			}
		}catch(Exception e)
		{
			log.error(e.getMessage());
			System.out.println(e.getMessage());
			throw e;
		}
		return queue;
	}

	public List<SyncQueue> Inserted(List<SyncQueue> vlauelist) throws Exception {
		try{
			Date dtime = this.getCurrent_date();
			
			List<CommonEntity> common_lst = new ArrayList<CommonEntity>();			
			for(SyncQueue obj : vlauelist)
			{
				if(CommonService.IsNull(obj.getQueueId()))
				{
					String id = super.SequenceNext("SYNC_QUEUE");					
					obj.setQueueId(CommonService.ConvertStringToBigDecimal(id));
					obj.setQueueSequence(CommonService.ConvertStringToBigDecimal(id));
					obj.setProcCreatedby("PROC-QUEUE");
					obj.setProcCreatedon(dtime);
					//obj.setProcUpdatedby("PROC-QUEUE");
					//obj.setProcUpdatedon(this.getCurrent_date());
					obj.setIsActive("Y");
					common_lst.add(obj);
				}
			}
			super.inserted(common_lst);
			
		}catch(Exception e)
		{
			log.error(e.getMessage());
			System.out.println(e.getMessage());
			throw e;
		}
		return vlauelist;
	}
	
	public List<SyncQueue> Updated(List<SyncQueue> vlauelist) throws Exception {
		try{
			Date dtime = this.getCurrent_date();
			List<CommonEntity> common_lst = new ArrayList<CommonEntity>();			
			for(SyncQueue obj : vlauelist)
			{
				if(!CommonService.IsNull(obj.getQueueId()))
				{
					obj.setProcUpdatedby("PROC-QUEUE");
					obj.setProcUpdatedon(dtime);
					common_lst.add(obj);					
				}
			}
			super.updated(common_lst);
			
		}catch(Exception e)
		{
			System.out.println(e.getMessage());
			log.error(e.getMessage());
			throw e;
		}
		return vlauelist;
	}
	
	public SyncQueue Updated(SyncQueue queue) throws Exception {
		try{
			
			queue.setProcUpdatedby("PROC-QUEUE");
			queue.setProcUpdatedon(this.getCurrent_date());
			
			super.updated(queue);
		}catch(Exception e)
		{
			System.out.println(e.getMessage());
			log.error(e.getMessage());
			throw e;
		}
		return queue;
	}
		
}
